package com.erp.framework.util;

import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.imageio.IIOImage;
import javax.imageio.ImageIO;
import javax.imageio.ImageWriter;
import javax.imageio.stream.ImageOutputStream;

import oracle.sql.BLOB;

import com.erp.app.common.ProjectConstants;
import com.erp.app.common.attachment.beans.AttachmentVo;
import com.erp.app.common.image.beans.ImageVo;
import com.erp.app.common.util.DocumentNumberGenerator;
import com.erp.framework.SimpleConstants;
import com.erp.framework.exceptions.CreateRecordException;
import com.erp.framework.exceptions.DbObjectException;
import com.erp.framework.exceptions.DeleteRecordException;
import com.erp.framework.exceptions.ModifyRecordException;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigDecimal;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.types.CustomUtilDate;
import com.erp.framework.util.logger.Logger;

public class DbObjectUtil {

	public static void execSql(String sqlQuery, ArrayList args, Connection conn) throws Exception {
		PreparedStatement pStmt = conn.prepareStatement(sqlQuery);
		final char token = '?';

		try {
			if (CommonUtil.isEmpty(sqlQuery)) {
				// the following error is for internal use only, English is ok
				throw new DbObjectException("Missing SQL Query.");
			}
			if ((args == null) || (args.size() == 0)) {
				// the following error is for internal use only, English is ok
				throw new DbObjectException("Missing SQL Query Arguments.");
			}

			if ( (CommonUtil.countStringToken(sqlQuery, token)) != (args.size()) ) {
				// the following error is for internal use only, English is ok
				throw new DbObjectException("Number of arguments not equal number of token('" + token + "') in SQL Query.");
			}

			Logger.sql("execSql(), sql=" + sqlQuery);

			// set the parameters
			for (int k = 0; (args != null) && (k < args.size()); k++) {
				Object obj = args.get(k);

				if(CommonUtil.isEmpty(obj)){
					//the following error is for internal use only, English is ok
					throw new DbObjectException("Parameter is empty: " + k);
				}

				//pStmt starts with 1, not 0, ==> use k+1
				if(obj instanceof String){
					pStmt.setString(k+1, (String)obj);
				} else if(obj instanceof java.sql.Date){
					pStmt.setDate(k+1, (java.sql.Date)obj);
				} else if(obj instanceof CustomSqlDate){
					pStmt.setDate(k+1, (CustomSqlDate)obj);
				} else if(obj instanceof CustomUtilDate){
					pStmt.setDate(k+1, new java.sql.Date(((CustomUtilDate)obj).getTime()));
				} else if(obj instanceof BigDecimal){
					pStmt.setBigDecimal(k+1, (BigDecimal)obj);
				} else if(obj instanceof BigInteger){
					pStmt.setBigDecimal(k+1, new BigDecimal(String.valueOf(obj)));
				} else if(obj instanceof CustomBigDecimal){
					pStmt.setBigDecimal(k+1, (CustomBigDecimal)obj);
				} else if(obj instanceof CustomBigInteger){
					pStmt.setBigDecimal(k+1, new BigDecimal(String.valueOf((CustomBigInteger)obj)));
                } else if(obj instanceof Timestamp){
                	pStmt.setTimestamp(k+1, (Timestamp) obj);
				} else {
					pStmt.setString(k+1, String.valueOf(obj));
				}
			}

			//pStmt must be a "INSERT", "UPDATE" or "DELETE" statements
			int count = pStmt.executeUpdate();
			if(count == 0){
				Logger.debug("execSql(), SQL is done successfully with 0 rows affected in database");
			} else if(count > 0){
				Logger.debug("execSql(), SQL is done successfully with " + count + " rows affected in database");
			} else {
				//if unsuccess, pStmt.getResultSet() <> null
				Logger.debug("execSql(), SQL is done unsuccessfully, pStmt.getResultSet() null? " + (pStmt.getResultSet() == null));
			}

		} catch (Exception e) {
			// The following exceptions is used to capture all Stored Proc,
			// Functions, View, Trigger, Sequence, etc
			Logger.logExceptions(e);
			throw new DbObjectException(e.getMessage());
		} finally {
			try {
				if (pStmt != null) {
					pStmt.close();
				}
			} catch (Exception e) {
				throw new DbObjectException(e.getMessage());
			}
		}
	}

	public void callFn(String spName, ArrayList args, Connection conn) throws Exception {
		//to be implemented when needed
	}

    public void callSp(String spName, Object[] args, Connection conn) throws Exception {
        this.callSp(spName, new ArrayList(Arrays.asList(args)), conn);
    }

	public  void callSp(String spName, ArrayList args, Connection conn) throws Exception {
		CallableStatement cStmt = null;
		StringBuffer query = new StringBuffer();

		try {
			if (CommonUtil.isEmpty(spName)) {
				// the following error is for internal use only, English is ok
				throw new DbObjectException("Missing Stored Proc name.");
			}
			if ((args == null) || (args.size() == 0)) {
				// the following error is for internal use only, English is ok
				throw new DbObjectException("Missing Stored Proc arguments.");
			}

			query.append("{ call ");
			query.append(spName);
			query.append("(?"); //for ret msg
			for (int k = 0; (args != null) && (k < args.size()); k++) {
				query.append(",?");
			}
			query.append(") }");
			Logger.sql(this, "callSp(), callableStatement is: " + query.toString());

			cStmt = conn.prepareCall(query.toString());
			cStmt.registerOutParameter(1, java.sql.Types.VARCHAR); // ret msg

			// set the parameters
			for (int k = 0; (args != null) && (k < args.size()); k++) {
				Object obj = args.get(k);

				if(CommonUtil.isEmpty(obj)){
					//the following error is for internal use only, English is ok
					throw new DbObjectException("At least one Stored Proc argument is empty.");
				}

				//skip the first ret msg, k + 2
				if(obj instanceof String){
					cStmt.setString(k + 2, (String)obj);
				} else if(obj instanceof java.sql.Date){
					cStmt.setDate(k + 2, (java.sql.Date)obj);
				} else if(obj instanceof CustomSqlDate){
					cStmt.setDate(k + 2, (CustomSqlDate)obj);
				} else if(obj instanceof CustomUtilDate){
					cStmt.setDate(k + 2, new java.sql.Date(((CustomUtilDate)obj).getTime()));
				} else if(obj instanceof BigDecimal){
					cStmt.setBigDecimal(k + 2, (BigDecimal)obj);
				} else if(obj instanceof BigInteger){
					cStmt.setBigDecimal(k + 2, new BigDecimal(String.valueOf(obj)));
				} else if(obj instanceof CustomBigDecimal){
					cStmt.setBigDecimal(k + 2, (CustomBigDecimal)obj);
				} else if(obj instanceof CustomBigInteger){
					cStmt.setBigDecimal(k + 2, new BigDecimal(String.valueOf((CustomBigInteger)obj)));
				} else if(obj instanceof Blob){
					cStmt.setBlob(k + 2, (Blob)obj);
				}else {
					//cStmt.setObject(k + 2, obj);
					//default set as String, instead of Object
					cStmt.setString(k + 2, String.valueOf(obj));
				}
			}
			cStmt.executeUpdate();
			String retMsg = cStmt.getString(1);

			Logger.debug(this, "callSp(), stored procedure [" + spName + "] is done with returned message: " + retMsg);
			if (!retMsg.equals(SimpleConstants.GENERIC_SUCCESS)) {
				throw new DbObjectException(retMsg);
			}

		} catch (Exception e) {
			// The following exceptions is used to capture all Stored Proc,
			// Functions, View, Trigger, Sequence, etc
			Logger.logExceptions(this, e);
			throw new DbObjectException(e.getMessage());
		} finally {
			try {
				if (cStmt != null) {
					cStmt.close();
				}
			} catch (Exception e) {
				throw new DbObjectException(e.getMessage());
			}
		}
	}

	public static String callSpWithRandomKey(String spName, ArrayList args, Connection conn) throws Exception {
		CallableStatement cStmt = null;
		StringBuffer query = new StringBuffer();
		String randomKey = "";

		try {
			if (CommonUtil.isEmpty(spName)) {
				// the following error is for internal use only, English is ok
				throw new DbObjectException("Missing Stored Proc name.");
			}
			if ((args == null) || (args.size() == 0)) {
				// the following error is for internal use only, English is ok
				throw new DbObjectException("Missing Stored Proc arguments.");
			}

			query.append("{ call ");
			query.append(spName);
			query.append("(?, ?"); //for ret msg and random key
			for (int k = 0; (args != null) && (k < args.size()); k++) {
				query.append(",?");
			}
			query.append(") }");
			Logger.sql("DbObjectUtil.callSpWithRandomKey(), callableStatement is: " + query.toString());

			cStmt = conn.prepareCall(query.toString());
			cStmt.registerOutParameter(1, java.sql.Types.VARCHAR); // ret msg
			cStmt.registerOutParameter(2, java.sql.Types.VARCHAR); // random key

			// set the parameters
			for (int k = 0; (args != null) && (k < args.size()); k++) {
				Object obj = args.get(k);

				if(CommonUtil.isEmpty(obj)){
					//the following error is for internal use only, English is ok
					throw new DbObjectException("At least one Stored Proc argument is empty.");
				}

				//skip the first ret msg, k + 2
				if(obj instanceof String){
					cStmt.setString(k + 3, (String)obj);
				} else if(obj instanceof java.sql.Date){
					cStmt.setDate(k + 3, (java.sql.Date)obj);
				} else if(obj instanceof CustomSqlDate){
					cStmt.setDate(k + 3, (CustomSqlDate)obj);
				} else if(obj instanceof CustomUtilDate){
					cStmt.setDate(k + 3, new java.sql.Date(((CustomUtilDate)obj).getTime()));
				} else if(obj instanceof BigDecimal){
					cStmt.setBigDecimal(k + 3, (BigDecimal)obj);
				} else if(obj instanceof BigInteger){
					cStmt.setBigDecimal(k + 3, new BigDecimal(String.valueOf(obj)));
				} else if(obj instanceof CustomBigDecimal){
					cStmt.setBigDecimal(k + 3, (CustomBigDecimal)obj);
				} else if(obj instanceof CustomBigInteger){
					cStmt.setBigDecimal(k + 3, new BigDecimal(String.valueOf((CustomBigInteger)obj)));
				} else {
					//cStmt.setObject(k + 3, obj);
					//default set as String, instead of Object
					cStmt.setString(k + 3, String.valueOf(obj));
				}
			}
			cStmt.executeUpdate();
			String retMsg = cStmt.getString(1);
			randomKey = cStmt.getString(2);

			Logger.debug("callSpWithRandomKey(), stored procedure [" + spName + "] is done with returned message: " + retMsg);
			if (!retMsg.equals(SimpleConstants.GENERIC_SUCCESS)) {
				throw new DbObjectException(retMsg);
			}

		} catch (Exception e) {
			// The following exceptions is used to capture all Stored Proc,
			// Functions, View, Trigger, Sequence, etc
			Logger.logExceptions(e);
			throw new DbObjectException(e.getMessage());
		} finally {
			try {
				if (cStmt != null) {
					cStmt.close();
				}
			} catch (Exception e) {
				throw new DbObjectException(e.getMessage());
			}
		}

		return randomKey;
	}

	public static interface ResultSetInterpreter {
        public Object interpret(ResultSet rs, int i) throws SQLException ;
    }

	private static final Map columnInterpreters = new HashMap();

	static {
        columnInterpreters.put(new Integer(Types.BIGINT), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getBigDecimal(i); }
        });
        columnInterpreters.put(new Integer(Types.CHAR), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getString(i); }
        });
        columnInterpreters.put(new Integer(Types.DATE), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getDate(i); }
        });
        columnInterpreters.put(new Integer(Types.DECIMAL), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getBigDecimal(i); }
        });
        columnInterpreters.put(new Integer(Types.DOUBLE), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return new Double(rs.getDouble(i)); }
        });
        columnInterpreters.put(new Integer(Types.FLOAT), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return new Float(rs.getFloat(i)); }
        });
        columnInterpreters.put(new Integer(Types.INTEGER), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return new Integer(rs.getInt(i)); }
        });
        columnInterpreters.put(new Integer(Types.NUMERIC), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getBigDecimal(i); }
        });
        columnInterpreters.put(new Integer(Types.TIMESTAMP), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getTimestamp(i); }
        });
        columnInterpreters.put(new Integer(Types.REAL), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getBigDecimal(i); }
        });
        columnInterpreters.put(new Integer(Types.VARCHAR), new ResultSetInterpreter() {
            public Object interpret(ResultSet rs, int i) throws SQLException { return rs.getString(i); }
        });
    }

    public static final ResultSetInterpreter EMPTY_STRING_INTERPRETOR = new ResultSetInterpreter() {
        public Object interpret(ResultSet rs, int i) throws SQLException {
            return "";
        }
    };

	public ResultSetInterpreter getResultSetInterpreter(int sqlType) {
        return this.getResultSetInterpreter(sqlType, EMPTY_STRING_INTERPRETOR);
    }

    public ResultSetInterpreter getResultSetInterpreter(int sqlType, ResultSetInterpreter defaultInterpreter) {
        return this.getResultSetInterpreter(new Integer(sqlType), defaultInterpreter);
    }

    public ResultSetInterpreter getResultSetInterpreter(Integer sqlType, ResultSetInterpreter defaultInterpreter) {
        return (columnInterpreters.containsKey(sqlType)) ? ((ResultSetInterpreter) columnInterpreters.get(sqlType)) : defaultInterpreter;
    }


    public static String getNextAttachmentLineNo(SimpleBaseVo model,Connection conn){
    	AttachmentVo attachVo = (AttachmentVo) model;
    	DocumentNumberGenerator dng = new DocumentNumberGenerator(attachVo
                .getCompanyCode(), ProjectConstants.DOC_GENERIC_ATTACHMENT,
                "LINE_NO", DocumentNumberGenerator.YEAR_IGNORED, attachVo
                        .getCreateUser(), "LINE_NO", conn);

        return dng.getSequence(5);
    }
    
    public static String getNextImageAttachmentLineNo(SimpleBaseVo model,Connection conn){
        AttachmentVo attachVo = (AttachmentVo) model;
        DocumentNumberGenerator dng = new DocumentNumberGenerator(attachVo
                .getCompanyCode(), ProjectConstants.DOC_GENERIC_ATTACHMENT,
                "LINE_NO", DocumentNumberGenerator.YEAR_IGNORED, attachVo
                        .getCreateUser(), "LINE_NO", conn);

        return dng.getSequence(5);
    }
    
    
    public static String getNextImageID(SimpleBaseVo model,Connection conn){
        AttachmentVo attachVo = (AttachmentVo) model;
        DocumentNumberGenerator dng = new DocumentNumberGenerator(attachVo
                .getCompanyCode(), ProjectConstants.DOC_IMAGE_ID,
                "IMAGE_ID", DocumentNumberGenerator.YEAR_IGNORED, attachVo
                        .getCreateUser(), "IMAGE_ID", conn);

        return dng.getSequence(5);
    }

    public void updateAttachment(AttachmentVo model, Connection conn)throws Exception{

		 String spName = "Sp_Update_Attachment";

		 PreparedStatement pStmt = null;
	     ResultSet rs = null;

	     try {

			//for (int i = 0; attachList.size() > 0 && i < attachList.size(); i++) {
				BLOB blob=null;
				rs = null;
				OutputStream outs=null;
				AttachmentVo attachVo = (AttachmentVo) model;
				if(!attachVo.getIsDelete()){
					if(!attachVo.getIsExisting()){
						attachVo.setAttachmentAttachmentLineNo(new CustomBigInteger(this.getNextAttachmentLineNo(attachVo,conn)));
						pStmt = conn.prepareStatement("INSERT INTO T_ATTACHMENT(COMPANY_CODE,LINE_NO,ATTACHMENT) VALUES(?,?,EMPTY_BLOB())");
						pStmt.setString(1,CommonUtil.nullToString(attachVo.getCompanyCode()));
						pStmt.setInt(2,attachVo.getAttachmentAttachmentLineNo().intValue());
						pStmt.executeUpdate();
					}

					pStmt = conn.prepareStatement("select ATTACHMENT from T_ATTACHMENT where COMPANY_CODE='"+attachVo.getCompanyCode()+
						    "' AND LINE_NO="+attachVo.getAttachmentAttachmentLineNo().intValue()+" for update");
					rs = pStmt.executeQuery();
					if(rs.next()){
						blob = (BLOB)rs.getBlob("ATTACHMENT");
						outs  = blob.getBinaryOutputStream();
						if(attachVo.getAttachment()!=null&&!(attachVo.getAttachment().length==0))
							outs.write(attachVo.getAttachment());
							else
							outs.write("     ".getBytes());
					}


					rs.close();
					ArrayList args = new ArrayList();
					args.add(attachVo.getCompanyCode());
					if(!CommonUtil.isEmpty(attachVo.getAttachmentContentType()))
					args.add(attachVo.getAttachmentContentType());
					else
						args.add("EMPTY");
					if(!CommonUtil.isEmpty(attachVo.getAttachmentDescription()))
						args.add(attachVo.getAttachmentDescription());
						else
							args.add("EMPTY");
					args.add(CommonUtil.isEmpty(attachVo.getUpdateUser()) ? attachVo
							.getCreateUser() : attachVo.getUpdateUser());
					args.add(attachVo.getAttachmentAttachmentLineNo());
					args.add(CommonUtil.isEmpty(attachVo.getAttachmentFileName())?"NoFileName":attachVo.getAttachmentFileName());
				    args.add(blob);

					this.callSp(spName, args, conn);
					outs.close();




					/*ArrayList args = new ArrayList();
					pStmt=conn.prepareStatement("select EMPTY_BLOB() CONTENT FROM DUAL FOR UPDATE");
					rs = pStmt.executeQuery();
					rs.next();
					BLOB newBlob = (BLOB)rs.getBlob("CONTENT");
					outs  = newBlob.getBinaryOutputStream();
					if(attachVo.getAttachment()!=null&&!(attachVo.getAttachment().length==0))
					outs.write(attachVo.getAttachment());
					else
					outs.write("     ".getBytes());
					args.add(attachVo.getCompanyCode());
					if(!CommonUtil.isEmpty(attachVo.getAttachmentContentType()))
					args.add(attachVo.getAttachmentContentType());
					else
						args.add("EMPTY");
					if(!CommonUtil.isEmpty(attachVo.getAttachmentDescription()))
						args.add(attachVo.getAttachmentDescription());
						else
							args.add("EMPTY");
					args.add(CommonUtil.isEmpty(attachVo.getUpdateUser()) ? attachVo
							.getCreateUser() : attachVo.getUpdateUser());
					args.add(attachVo.getAttachmentAttachmentLineNo());
					args.add(CommonUtil.isEmpty(attachVo.getAttachmentFileName())?"NoFileName":attachVo.getAttachmentFileName());
				    args.add(newBlob);
					this.callSp(spName, args, conn);*/
				}else {

					pStmt = conn.prepareStatement("DELETE FROM T_ATTACHMENT WHERE COMPANY_CODE=? AND LINE_NO=?");
					pStmt.setString(1,CommonUtil.nullToString(attachVo.getCompanyCode()));
					pStmt.setInt(2,attachVo.getAttachmentAttachmentLineNo().intValue());
					pStmt.executeUpdate();
				}


			//}

		} catch (Exception e) {
			Logger.logExceptions(this, e);
			throw new DbObjectException(e.getMessage());
		} finally {
           try {
               if (rs != null)
                   rs.close();
               if (pStmt != null)
                   pStmt.close();
           } catch (SQLException sqe) {
               Logger.logExceptions(this, sqe);
           }
       }


   }

    public static void insertDbAttachment(AttachmentVo vo, Connection conn) throws CreateRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
//            conn.setAutoCommit(false);
    		String query = "INSERT INTO T_ATTACHMENT ("
    						+ "COMPANY_CODE, LINE_NO, ATTACHMENT_DESC, CONTENT_TYPE, ATTACHMENT"
    						+ ", FILE_NAME, CREATE_USER, CREATE_DATE, DC_IND) "
    						+ " VALUES("
    						+ "?, ?, ?, ?, EMPTY_BLOB()"
    						+ ", ?, ?, SYSDATE, 'NO'"
    						+ ")";
            pStmt = conn.prepareStatement(query);
    		int i = 1;
    		pStmt.setString(i++, vo.getCompanyCode());
    		pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
    		setNullableString(pStmt, i++, vo.getAttachmentDescription());
    		setNullableString(pStmt, i++, vo.getAttachmentContentType());
    		setNullableString(pStmt, i++, vo.getAttachmentFileName());
    		setNullableString(pStmt, i++, vo.getCreateUser());
            int count = pStmt.executeUpdate();
            if (count != 1)
                throw new SQLException("Unable to insert " + vo.getPrimaryKey());

            if(vo.getAttachment()!=null){
                query = "select ATTACHMENT from T_ATTACHMENT where COMPANY_CODE=? AND LINE_NO=?";
                pStmt = conn.prepareStatement(query);
        		pStmt.setString(1, vo.getCompanyCode());
        		pStmt.setInt(2, vo.getAttachmentAttachmentLineNo().intValue());
    			rs = pStmt.executeQuery();
    			if(rs.next()){
        			BLOB blob=(BLOB) rs.getBlob("ATTACHMENT");  
        			OutputStream out=blob.getBinaryOutputStream();  
        			try {
        				out.write(vo.getAttachment());
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
        			try {
        				out.close();
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
    			}else{
                    throw new SQLException("Unable to insert " + vo.getPrimaryKey());
    			}
            }
//			conn.commit();
        } catch (SQLException sqe) {
            throw new CreateRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }

    public static void updateDbAttachment(AttachmentVo vo, Connection conn) throws ModifyRecordException {
    	updateDbAttachment(vo, conn, true);
    }

    public static void updateDbAttachment(AttachmentVo vo, Connection conn, boolean isHandleAttachment) throws ModifyRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
    		String query = "UPDATE T_ATTACHMENT "
    			+ "SET ATTACHMENT_DESC=?, CONTENT_TYPE=?, FILE_NAME=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE ";
    		if(vo.getAttachment() == null){
    			query += ", ATTACHMENT=EMPTY_BLOB() ";
    		}
    		query += "WHERE COMPANY_CODE=? AND LINE_NO=? ";
        	pStmt = conn.prepareStatement(query);
    		int i = 1;
    		setNullableString(pStmt, i++, vo.getAttachmentDescription());
    		setNullableString(pStmt, i++, vo.getAttachmentContentType());
    		setNullableString(pStmt, i++, vo.getAttachmentFileName());
    		setNullableString(pStmt, i++, vo.getUpdateUser());
    		pStmt.setString(i++, vo.getCompanyCode());
    		pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
            
    		int count = pStmt.executeUpdate();
            if (count != 1) {                
                throw new SQLException("Unable to update " + vo.getPrimaryKey());
            }

            if(vo.getAttachment()!=null && isHandleAttachment){
                query = "select ATTACHMENT from T_ATTACHMENT where COMPANY_CODE=? AND LINE_NO=?";
                pStmt = conn.prepareStatement(query);
        		pStmt.setString(1, vo.getCompanyCode());
        		pStmt.setInt(2, vo.getAttachmentAttachmentLineNo().intValue());
    			rs = pStmt.executeQuery();
    			if(rs.next()){
        			BLOB blob=(BLOB) rs.getBlob("ATTACHMENT");  
        			OutputStream out=blob.getBinaryOutputStream();  
        			try {
        				out.write(vo.getAttachment());
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
        			try {
        				out.close();
        			} catch (IOException e) {
        				// TODO Auto-generated catch block
        				e.printStackTrace();
        			}
    			}else{
                    throw new SQLException("Unable to update " + vo.getPrimaryKey());
    			}
            }
        } catch (SQLException sqe) {
            throw new ModifyRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }

    public static void deleteDbAttachment(AttachmentVo vo, Connection conn) throws DeleteRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
    		String query = "DELETE FROM T_ATTACHMENT WHERE COMPANY_CODE=? AND LINE_NO=? ";
            pStmt = conn.prepareStatement(query);
    		int i = 1;
    		pStmt.setString(i++, vo.getCompanyCode());
    		pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
            int count = pStmt.executeUpdate();
            if (count != 1) {
                Logger.warn("Execute Update record count != 1: " + count);
            }
        } catch (SQLException sqe) {
            throw new DeleteRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }
    
    public static void deleteDbImage(ImageVo vo, Connection conn) throws DeleteRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
                      
            
            String query = "DELETE FROM t_attachment " 
                + " WHERE COMPANY_CODE=? AND line_no IN (SELECT attachment_line_no" 
                + " FROM t_image_level"
                + " WHERE image_id = ? AND COMPANY_CODE = ?)";
            pStmt = conn.prepareStatement(query);
            int i = 1;
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setInt(i++, vo.getImage_Id().intValue());
            pStmt.setString(i++, vo.getCompanyCode());
            int count = pStmt.executeUpdate();
            if (count != 1) {
                Logger.warn("Execute Update record count != 1: " + count);
            }
            
             query = "DELETE FROM T_IMAGE_LEVEL WHERE COMPANY_CODE=? AND IMAGE_ID=? ";
            pStmt = conn.prepareStatement(query);
             i = 1;
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setInt(i++, vo.getImage_Id().intValue());
             count = pStmt.executeUpdate();
            if (count != 1) {
                Logger.warn("Execute Update record count != 1: " + count);
            }
            
            query = "DELETE FROM T_IMAGE WHERE COMPANY_CODE=? AND IMAGE_ID=? ";
            pStmt = conn.prepareStatement(query);
             i = 1;
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setInt(i++, vo.getImage_Id().intValue());
             count = pStmt.executeUpdate();
            if (count != 1) {
                Logger.warn("Execute Update record count != 1: " + count);
            }
         } catch (SQLException sqe) {
            throw new DeleteRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }

    public static void insertDbImage(ImageVo vo, Connection conn) throws CreateRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {

            String query = "INSERT INTO T_IMAGE (" 
                + "COMPANY_CODE, IMAGE_ID, CREATE_USER, CREATE_DATE) " 
                + " VALUES("
                + "?, ?, ?, SYSDATE" + ")";
            pStmt = conn.prepareStatement(query);
            int i = 1;
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setInt(i++, vo.getImage_Id().intValue());
            setNullableString(pStmt, i++, vo.getCreateUser());
            int count = pStmt.executeUpdate();
            if (count != 1)
                throw new SQLException("Unable to insert " + vo.getPrimaryKey());

            query = "INSERT INTO T_IMAGE_LEVEL (" 
                + "COMPANY_CODE, IMAGE_ID, LEVEL_ID, IMAGE_HEIGH, IMAGE_WIDTH, ATTACHMENT_LINE_NO, CREATE_USER, CREATE_DATE) " 
                + " VALUES("
                + "?, ?, ?, ?, ?, ?, ?, SYSDATE" + ")";
            pStmt = conn.prepareStatement(query);
            i = 1;
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setInt(i++, vo.getImage_Id().intValue());
            pStmt.setInt(i++, 1);
            setNullableBigDecimal(pStmt, i++, vo.getImage_Heigh());
            setNullableBigDecimal(pStmt, i++, vo.getImage_Width());
            pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
            setNullableString(pStmt, i++, vo.getCreateUser());
            count = pStmt.executeUpdate();
            
            if (count != 1)
                throw new SQLException("Unable to insert " + vo.getPrimaryKey());
            
            insertDbAttachment(vo, conn);
            
            
            pStmt = conn.prepareStatement(query);
            vo.setAttachmentAttachmentLineNo(new CustomBigInteger(getNextAttachmentLineNo(vo, conn)));
            vo.setAttachment(ImageUtil.convertImage(ProjectConstants.IMAGE_MAX_HEIGHT, ProjectConstants.IMAGE_MAX_WIDTH, vo.getAttachmentContentType(), vo.getAttachment()));
            i = 1;
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setInt(i++, vo.getImage_Id().intValue());
            pStmt.setInt(i++, 2);
            setNullableBigDecimal(pStmt, i++, vo.getImage_Heigh());
            setNullableBigDecimal(pStmt, i++, vo.getImage_Width());
            pStmt.setInt(i++, vo.getAttachmentAttachmentLineNo().intValue());
            setNullableString(pStmt, i++, vo.getCreateUser());
            count = pStmt.executeUpdate();
            if (count != 1)
                throw new SQLException("Unable to insert " + vo.getPrimaryKey());
            insertDbAttachment(vo, conn);
            
        } catch (Exception sqe) {
            throw new CreateRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                Logger.logExceptions(sqe);
            }
        }
    }

    public static void updateDbImage(ImageVo vo, Connection conn, boolean isHandleAttachment) throws ModifyRecordException,
            IOException {
        updateDbAttachment(vo, conn, isHandleAttachment);

        vo.setAttachmentAttachmentLineNo(new CustomBigInteger(vo.getAttachmentAttachmentLineNo().intValue() + 1));
        if (vo.getAttachment()!=null && isHandleAttachment) {
            vo.setAttachment(ImageUtil.convertImage(ProjectConstants.IMAGE_MAX_HEIGHT, ProjectConstants.IMAGE_MAX_WIDTH, vo.getAttachmentContentType(), vo.getAttachment()));
        }
        updateDbAttachment(vo, conn, isHandleAttachment);
    }
    
    private static void setNullableString(PreparedStatement pStmt, int index, String str) throws SQLException {
        if (str == null)
            pStmt.setNull(index, java.sql.Types.VARCHAR);
        else
            pStmt.setString(index, str);
    }

    protected static void setNullableBigDecimal(PreparedStatement pStmt, int index, BigDecimal bigDecimal) throws SQLException {

        if (bigDecimal == null)
            pStmt.setNull(index, java.sql.Types.DECIMAL);
        else
            pStmt.setBigDecimal(index, bigDecimal);
    }
    

}
